<?php

namespace app\admin\service;

use app\admin\model\BoxAdMedia;
use app\admin\model\BoxDataOverview;
use app\admin\model\BoxMemberRemainLog;
use app\admin\model\BoxStatisticsAdLtvLog;
use app\admin\model\BoxStatisticsAdOverviewLog;
use app\admin\model\Package;
use app\admin\model\UserPromote;

class BoxStatisticsAdOverviewService extends BaseService
{

    /**
     * @Notes:广告投放统计
     * @Interface getList
     * @return array
     */
    public function getList()
    {
        $param = request()->param();

        $map = [];

        if (!empty($param['time_range'])) {
            $param['time_range'][0] = implode('', explode('-', $param['time_range'][0]));
            $param['time_range'][1] = implode('', explode('-', $param['time_range'][1]));
            $map[]                  = ['day_time', '>=', $param['time_range'][0]];
            $map[]                  = ['day_time', '<=', $param['time_range'][1]];
        }

        if (isset($param['media_id']) && !empty($param['media_id'])) {
            $map[] = ['media_id', '=', $param['media_id']];
        }

        if (isset($param['promote_id']) && !empty($param['promote_id'])) {
            $map[] = ['promote_id', '=', $param['promote_id']];
        }

        if (isset($param['platform']) && !empty($param['platform'])) {
            $map[] = ['platform', '=', $param['platform']];
        }

        $list = (new BoxStatisticsAdOverviewLog())
            ->field('day_time,platform,package_id,media_id,promote_id,pv,active_num,register_num,pay_people_num,pay_amount,cost,all_pay_account_num,all_pay_amount')
            ->where($map)
            ->order('day_time', 'desc')
            ->select()->toArray();
        //获取数据总数
        $count = count($list);

        //媒体
        $promoteData = UserPromote::field(['id', 'realname'])->select()->toArray();
        $promoteData = array_column($promoteData, 'realname', 'id');

        //产品
        $packageData            = Package::field(['id', 'package_name'])->select()->toArray();
        $packageData            = array_column($packageData, 'package_name', 'id');
        $packageData['9999997'] = '千年游戏盒子pc';
        $packageData['9999998'] = '千年游戏盒子ios';
        $packageData['9999999'] = '千年游戏盒子android';

        //投放渠道
        $mediaData = BoxAdMedia::field(['id', 'name'])->select()->toArray();
        $mediaData = array_column($mediaData, 'name', 'id');

        if (!empty($list)) {
            $pv_all              = 0;
            $active_num_all      = 0;
            $register_num_all    = 0;
            $pay_people_num_all  = 0;
            $pay_amount_all      = 0;
            $all_pay_account_num = 0;
            $all_pay_amount      = 0;
            $cost_all            = 0;
            foreach ($list as $key => &$item) {
                $item['media_name']   = $mediaData[$item['media_id']] ?? '';
                $item['promote_name'] = $promoteData[$item['promote_id']] ?? '';
                $item['package_name'] = $packageData[$item['package_id']] ?? '';
                $item['active_rate']  = empty($item['pv']) ? '0%' : round($item['active_num'] / $item['pv'] * 100, 2) . "%";
                if (empty($item['cost']) || $item['cost'] == '0.00') {
                    $item['roi']     = 0;
                    $item['all_roi'] = 0;
                } else {
                    $item['roi']     = round($item['pay_amount'] / $item['cost'] * 100, 2) . "%";
                    $item['all_roi'] = round($item['all_pay_amount'] / $item['cost'] * 100, 2) . "%";
                }
                $item['day_time'] = date("Y-m-d", strtotime($item['day_time']));

                $pv_all              += $item['pv'];
                $active_num_all      += $item['active_num'];
                $register_num_all    += $item['register_num'];
                $pay_people_num_all  += $item['pay_people_num'];
                $pay_amount_all      += $item['pay_amount'];
                $all_pay_account_num += $item['all_pay_account_num'];
                $all_pay_amount      += $item['all_pay_amount'];
                $cost_all            += $item['cost'];
            }

            $list[$count]['day_time']            = "汇总";
            $list[$count]['media_name']          = "-";
            $list[$count]['package_name']        = "-";
            $list[$count]['platform']            = "-";
            $list[$count]['promote_name']        = "-";
            $list[$count]['pv']                  = $pv_all;
            $list[$count]['active_num']          = $active_num_all;
            $list[$count]['active_rate']         = empty($pv_all) ? '0%' : round($active_num_all / $pv_all / $count * 100, 2) . "%";
            $list[$count]['register_num']        = $register_num_all;
            $list[$count]['pay_people_num']      = $pay_people_num_all;
            $list[$count]['pay_amount']          = $pay_amount_all;
            $list[$count]['roi']                 = empty($cost_all) ? '0%' : round($pay_amount_all / $cost_all * 100, 2) . "%";
            $list[$count]['all_pay_account_num'] = $all_pay_account_num;
            $list[$count]['all_pay_amount']      = $all_pay_amount;
            $list[$count]['all_roi']             = empty($cost_all) ? '0%' : round($all_pay_amount / $cost_all * 100, 2) . "%";
            $list[$count]['cost']                = $cost_all;
        }
        //返回结果
        return [
            "msg"   => '操作成功',
            "code"  => 0,
            "data"  => $list,
            "count" => $count,
        ];
    }

    public function adOverviewDownloadUpload()
    {
        $param = request()->param();
        // 上传文件(非图片)
        $result = upload_file('file', '', $error);
        if (!$result) {
            return $this->jsonReturn(message($error, false));
        }

        // 文件路径
        if (is_array($result)) {
            $result = $result['file_name'];
        }
        $filePath = UPLOAD_TEMP_PATH . '/' . $result;
        if (!file_exists($filePath)) {
            return $this->jsonReturn(message("文件不存在", false));
        }
        // 读取文件
        $objPHPExcel   = \PHPExcel_IOFactory::load($filePath); //获取sheet表格数目
        $sheetSelected = 0;
        $objPHPExcel->setActiveSheetIndex($sheetSelected); //获取表格行数
        $rowCount = $objPHPExcel->getActiveSheet()->getHighestRow(); //获取表格列数
        // 循环读取行数据
        for ($row = 2; $row <= $rowCount; $row++) {
            $date      = $objPHPExcel->getActiveSheet()->getCell("A" . $row)->getValue();
            $mediaId   = $objPHPExcel->getActiveSheet()->getCell("B" . $row)->getValue();
            $packageId = $objPHPExcel->getActiveSheet()->getCell("D" . $row)->getValue();
            $platform  = $objPHPExcel->getActiveSheet()->getCell("F" . $row)->getValue();
            $promoteId = $objPHPExcel->getActiveSheet()->getCell("H" . $row)->getValue();
            $cost      = $objPHPExcel->getActiveSheet()->getCell("J" . $row)->getValue();

            $map   = [];
            $map[] = ['day_time', '=', implode('', explode('-', $date))];
            $map[] = ['media_id', '=', $mediaId];
            $map[] = ['package_id', '=', $packageId];
            $map[] = ['platform', '=', $platform];
            $map[] = ['promote_id', '=', $promoteId];

            (new BoxStatisticsAdOverviewLog())->where($map)->update(['cost' => $cost]);
        }

        return message("上传成功", true);
    }

}
